﻿using EntAppFrameWork;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BuildRWSView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            String Content = "<span style=\"font-family:SimSun;font-size:14px;line-height:1.5;\">&nbsp;&nbsp;&nbsp;&nbsp;设水箱的高变为<img src=\"/FileProgress/Download2/82073\">，填写下表：<br></span><span style=\"font-family:SimSun;font-size:14px;line-height:1.5;\">&nbsp;&nbsp;&nbsp;&nbsp;设水箱的高变为<img src=\"/FileProgress/Download2/82074 >，填写下表：<br></span> <span style=\"font-family:SimSun;font-size:14px;line-height:1.5;\">&nbsp;&nbsp;&nbsp;&nbsp;设水箱的高变为<img src=\"/FileProgress/Download2/820751 \">，填写下表：<br></span><span style=\"font-family:SimSun;font-size:14px;line-height:1.5;\">&nbsp;&nbsp;&nbsp;&nbsp;设水箱的高变为<img src=\"/FileProgress/Download2/82076\" >，填写下表：<br></span>";
            var regexImg = new Regex("(?<=[\\s\\S]*/FileProgress/Download2/)\\d+(?=[\"]*.+)");
            var content = Content;
            var imgPaths = regexImg.Matches(content);

            StringBuilder patternBuilder = new StringBuilder(218);
            patternBuilder.Append(@"(?<=[\s\b]+");
            patternBuilder.Append(@"(");
            patternBuilder.Append(@"(from)|");
            patternBuilder.Append(@"(left[\s\b]+join)|");
            patternBuilder.Append(@"(left[\s\b]+outer[\s\b]+join)|");
            patternBuilder.Append(@"(right[\s\b]+join)|");
            patternBuilder.Append(@"(right[\s\b]+outer[\s\b]+join)|");
            patternBuilder.Append(@"(inner[\s\b]+join)|");
            patternBuilder.Append(@"(cross[\s\b]+join)");
            patternBuilder.Append(@")");
            patternBuilder.Append(@"[\s\b]+[\[]*)\w+(?=[\]]*[\s\b]*)");
            string pattern = patternBuilder.ToString();
            Regex r = new Regex(pattern, RegexOptions.IgnoreCase);
            ;
            foreach (Match i in r.Matches("select * from mytable"))
            {
                string abc = i.ToString();
            }
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String readDB = textBox1.Text;
            DataTable dt = null;
            EFC.DataAccess.DataAccess.ExecuteSQL("SELECT Name FROM SysObjects Where XType='U' and [name] not in ('sysdiagrams') ORDER BY Name", null, out dt);
            if (dt != null && dt.Rows.Count != 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    String tableName = dr[0].ToString();
                    //判断视图是否已存在，存在则删除
                    String sql = @"IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_"+tableName+@"')
                            BEGIN
                              drop view View_" + tableName + @"
                            END";
                    EFC.DataAccess.DataAccess.ExecuteNonQuery(sql);
                    //主键
                    sql = "SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='" + tableName + "' order by ORDINAL_POSITION";

                    DataTable keyTable = null;
                    EFC.DataAccess.DataAccess.ExecuteSQL(sql, null, out keyTable);
                    if (keyTable == null || keyTable.Rows.Count == 0)
                        continue;

                    String keyColumn = keyTable.Rows[0]["COLUMN_NAME"].ToString();

                    //创建视图
                    sql = @"create view View_" + tableName + " as " + @"SELECT   dbo." + tableName + @".*
                        FROM      dbo." + tableName + @"

                        UNION
                        SELECT   *
                        FROM      " + readDB + @".dbo." + tableName + @"
                        WHERE   NOT EXISTS
                                            (SELECT   " + keyColumn + @"
                                             FROM      dbo." + tableName + " where "+ keyColumn + "=" + readDB + @".dbo." + tableName + @"." + keyColumn+")";
                    EFC.DataAccess.DataAccess.ExecuteNonQuery(sql);
                }

                FixViewLoad fvl = new FixViewLoad();
                fvl.Load();
                fvl.Action();
                MessageBox.Show("创建成功！");
            }
            else {
                MessageBox.Show("无视图被创建！");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = null;
            EFC.DataAccess.DataAccess.ExecuteSQL("SELECT Name FROM SysObjects Where XType='U' ORDER BY Name", null, out dt);
            if (dt != null && dt.Rows.Count != 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    String tableName = dr[0].ToString();

                    String sql = "truncate table " + tableName;
                    EFC.DataAccess.DataAccess.ExecuteNonQuery(sql);
                }
                MessageBox.Show("清除成功！");
            }
            else
            {
                MessageBox.Show("无数据被清除！");
            }
        }

        private void fixOneToMultiHiddenView() { 
        
        }
    }
}
